package com.dao;

import com.model.Book;
import com.util.DBUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    public List<Book> listBooks(Connection con) throws SQLException {
        String sql = "select b.id, b.name, b.price, a.name as a_name from book b\n" +
                "    left join r_author_book rab on b.id = rab.book_id\n" +
                "    left join author a on rab.author_id = a.id\n" +
                "    order by b.id;";
        PreparedStatement pre = null;
        ResultSet rs = null;
        List<Book> books = new ArrayList<>();
        try {
            rs = DBUtil.query(con, pre, rs, sql, null);
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("name"));
                book.setPrice(rs.getFloat("price"));
                book.setAuthorName(rs.getString("a_name"));
                books.add(book);
            }
        } catch (SQLException e) {
            throw e;
        }
        DBUtil.closeResource(null, pre, rs);
        return books;
    }
    public List<Book> searchBook(Connection con,String name) throws SQLException {
        String sql = "select b.id, b.name, b.price, a.name as a_name from book b\n" +
                "    left join r_author_book rab on b.id = rab.book_id\n" +
                "    left join author a on rab.author_id = a.id\n" +
                "    where b.name =?";
        PreparedStatement pre = null;
        ResultSet rs = null;
        List<Book> books = new ArrayList<>();
        try {
            Object[] obj={name};
            rs = DBUtil.query(con, pre, rs, sql, obj);
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("name"));
                book.setPrice(rs.getFloat("price"));
                book.setAuthorName(rs.getString("a_name"));
                books.add(book);
            }
        } catch (SQLException e) {
            throw e;
        }
        DBUtil.closeResource(null, pre, rs);
        return books;
    }

    public int addBook(Connection con, Book book) throws SQLException {
        Object[] obj = {book.getName(), book.getPrice()};
        String sql = "insert into book(`name`, `price`) values(?, ?)";
        PreparedStatement pre = null;
        int updateRows = 0;

        try {
            updateRows = DBUtil.execute(con, pre, sql, obj);
        } catch (SQLException e) {
            throw e;
        }

        DBUtil.closeResource(null, pre, null);
        return updateRows;
    }

    public int updateBook(Connection con, Book book) throws SQLException {
        Object[] obj = {book.getName(), book.getPrice(), book.getId()};
        String sql = "update book set name=?,price=? where id=?";
        PreparedStatement pre = null;
        int updateRows = 0;

        try {
            updateRows = DBUtil.execute(con, pre, sql, obj);
        } catch (SQLException e) {
            throw e;
        }

        DBUtil.closeResource(null, pre, null);
        return updateRows;
    }

    public int deleteBookById(Connection con, int id) throws SQLException {
        Object[] obj = {id};
        String sql = "delete from book where id=?";
        PreparedStatement pre = null;
        int updateRows = 0;

        try {
            updateRows = DBUtil.execute(con, pre, sql, obj);
        } catch (SQLException e) {
            throw e;
        }

        DBUtil.closeResource(null, pre, null);
        return updateRows;
    }
    public int addRelation(Connection con, int authorId, int bookId) throws SQLException {
        Object[] obj = {authorId, bookId};
        String sql = "insert into r_author_book(author_id,book_id) values (?,?);";
        PreparedStatement pre = null;
        int updateRows = 0;

        try {
            updateRows = DBUtil.execute(con, pre, sql, obj);
        } catch (SQLException e) {
            throw e;
        }

        DBUtil.closeResource(null, pre, null);
        return updateRows;
    }

}
